# -*- coding: UTF-8 -*-

from sqlalchemy import create_engine
import pymysql
import pandas as pd


class mysqlhelper():
    def __init__(self):
        self.__mysqlurl = 'mysql+pymysql://futu:futu@localhost:3306/FutuDB'
        self.__username = 'root'
        self.__pwd = 'root'
        self.db = pymysql.connect(host=server,
                                  user=user,
                                  password=pwd,
                                  database=dbname)

    def savedf(self, df, tablename, if_exists='append'):
        try:
            conn = create_engine(self.mysqlurl, encoding='utf8')

            pd.io.sql.to_sql(df,
                             tablename,
                             conn,
                             if_exists=if_exists,
                             index=False)
        except Exception as e:
            print('err:', e)

    def CreateDB(dbname):
        try:
            conn = db.connect(self.__mysqlurl,
                              self.__username,
                              self__pwd,
                              charset='utf8')

            sql = 'create database {};'.format(dbname)
            cursor = conn.cursor()
            cursor.execute(sql)

            print('创建{}成功'.format(dbname))
        except:
            print("Unexpected CreateDB error:", sys.exc_info()[0])

            print('创建{}失败'.format(dbname))
            pass
        finally:
            cursor.close()
            conn.close()

    def CreateTable(dbname, sql):
        try:
            conn = db.connect(self.__mysqlurl,
                              self.__username,
                              self__pwd,
                              charset='utf8')

            cursor = conn.cursor()
            cursor.execute(sql)
            print('在{}中创建表成功'.format(dbname))
        except:
            print("Unexpected CreateTable error:", sys.exc_info()[0])
            print('在{}中创建表失败'.format(dbname))
            pass
        finally:
            cursor.close()
            conn.close()

    def Insert(dbname, sql):
        try:
            conn = db.connect(self.__mysqlurl,
                              self.__username,
                              self__pwd,
                              charset='utf8')

            cursor = conn.cursor()
            cursor.execute(sql)
            print('执行成功')
        except:
            print("Unexpected Insert error:", sys.exc_info()[0])
            print('执行失败')
        finally:
            cursor.close()
            conn.close()

    def InsertMany(dbname, sql, val):
        try:
            conn = db.connect(self.__mysqlurl,
                              self.__username,
                              self__pwd,
                              charset='utf8')

            cursor = conn.cursor()
            cursor.executemany(sql, val)
            conn.commit()

            data2 = cursor.fetchone()

            print('执行成功')
        except:
            print("Unexpected InsertMany error:", sys.exc_info()[0])
            print('执行失败')
        finally:
            cursor.close()
            conn.close()
